package projects;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;

@SuppressWarnings("serial")
public class task implements Serializable{
	private Integer taskId;
	private Integer milestoneId;
	private Integer resolutionId;
	private Integer statusId;
	private Date startDate;
	private Date endDate;
	private String taskName;
	private String taskDescription;
	private Integer taskPriorityId;
	private Integer taskTypeId;
	private Double taskEffort;
	
	public Integer getTaskId() {
		return taskId;
	}
	
	public void setTaskId(Integer taskId) {
		this.taskId = taskId;
	}
	public Integer getResolutionId() {
		return resolutionId;
	}

	public void setResolutionId(Integer resolutionId) {
		this.resolutionId = resolutionId;
	}
	public Integer getMilestoneId() {
		return milestoneId;
	}
	
	public void setMilestoneId(Integer milestoneId) {
		this.milestoneId = milestoneId;
	}
	
	public Integer getStatusId() {
		return statusId;
	}
	
	public void setStatusId(Integer statusId) {
		this.statusId = statusId;
	}
	
	public String getStartDate() {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
		return format.format(startDate);
	}
	
	public void setStartDate(String startDate) throws ParseException {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
		this.startDate = format.parse(startDate);
	}
	
	public String getEndDate() {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
		return format.format(endDate);
	}
	
	public void setEndDate(String endDate) throws ParseException {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
		this.endDate = format.parse(endDate);
	}
	
	public String getTaskName() {
		return taskName;
	}
	
	public void setTaskName(String taskName) {
		this.taskName = taskName;
	}
	
	public String getTaskDescription() {
		return taskDescription;
	}
	
	public void setTaskDescription(String taskDescription) {
		this.taskDescription = taskDescription;
	}
	
	public Integer getTaskPriorityId() {
		return taskPriorityId;
	}
	
	public void setTaskPriorityId(Integer taskPriorityId) {
		this.taskPriorityId = taskPriorityId;
	}
	
	public Integer getTaskTypeId() {
		return taskTypeId;
	}
	
	public void setTaskTypeId(Integer taskTypeId) {
		this.taskTypeId = taskTypeId;
	}
	
	public Double getTaskEffort() {
		return taskEffort;
	}
	
	public void setTaskEffort(Double taskEffort) {
		this.taskEffort = taskEffort;
	}

//-------------------> creates a new task for a specific milestone (startDate, endDate, taskName, taskDescription, taskPriorityId, taskTypeId, taskEffort)<-----------------------------------	
	
	public Integer createTask(Integer EmployeeId){
		Connection con = null;
		Integer taskId = 0;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement insertTask = (PreparedStatement) con.prepareStatement("insert into tasks(milestoneId, startDate, endDate, taskName, taskDescription, taskPriorityId, TaskTypeId, taskEffort, createdBy) values(?, ?, ?, ?, ?, ?, ?, ?, ?)", PreparedStatement.RETURN_GENERATED_KEYS);
			
			insertTask.setInt(1, this.getMilestoneId());
			insertTask.setString(2, this.getStartDate());
			insertTask.setString(3, this.getEndDate());
			insertTask.setString(4, this.getTaskName());
			insertTask.setString(5, this.getTaskDescription());
			insertTask.setInt(6, this.getTaskPriorityId());
			insertTask.setInt(7, this.getTaskTypeId());
			insertTask.setDouble(8, this.getTaskEffort());
			insertTask.setInt(9, EmployeeId);
			Integer result = insertTask.executeUpdate();
			
			if (result == 1){
				ResultSet rs = insertTask.getGeneratedKeys();
				
				while(rs.next()){
					this.setTaskId(rs.getInt(1));
					this.insertStatusResolution(rs);
					this.insertTeamTask(rs);
					taskId = rs.getInt(1);
				}
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return taskId;
	}
	
	public boolean insertTeamTask(ResultSet rs){
		Connection con = null;
		boolean isExecuted =false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement insertTask = (PreparedStatement) con.prepareStatement("insert into teamTasks (employeeId, taskId) values (1, ?)");
			insertTask.setInt(1, rs.getInt(1));
			Integer result = insertTask.executeUpdate();
			
			if (result == 1){
				isExecuted = true;
				}
			}
			catch (Exception e){
				System.out.println(e.getMessage());
			}
			finally{
				try {
					if(con != null)
					con.close();
				} 
			catch(Exception e) {}
		}
	return isExecuted;
	}
	
	public boolean insertStatusResolution(ResultSet rs){
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
					
			PreparedStatement insertStatusResolution = (PreparedStatement) con.prepareStatement("insert into statusResolutions(taskId, statusId, resolutionId, fromEmployeeId, toEmployeeId) values(?, ?, ?, 1, 1)");
			insertStatusResolution.setInt(1, rs.getInt(1));
			insertStatusResolution.setInt(2, this.getStatusId());
			insertStatusResolution.setInt(3, this.getResolutionId());
			Integer result = insertStatusResolution.executeUpdate();

			if(result == 1){
			isExecuted = true;
			}	
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}
	
//-------------------> updates the general information of a specific task (startDate, endDate, taskName, taskDescription, taskPriorityId, taskTypeId, taskEffort) <----------------------------	
	
	public boolean updateTask(){
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement updateTask = (PreparedStatement) con.prepareStatement("update tasks set startDate = ?, endDate = ?, taskName = ?, taskDescription = ?, taskPriorityId = ?, taskTypeId = ?, taskEffort = ? where taskId = ?");
			updateTask.setString(1, this.getStartDate());
			updateTask.setString(2, this.getEndDate());
			updateTask.setString(3, this.getTaskName());
			updateTask.setString(4, this.getTaskDescription());
			updateTask.setDouble(5, this.getTaskPriorityId());
			updateTask.setDouble(6, this.getTaskTypeId());
			updateTask.setDouble(7, this.getTaskEffort());
			updateTask.setInt(8, this.getTaskId());
			Integer result = updateTask.executeUpdate();
			
			if (result == 1){
				isExecuted = true;
				return isExecuted;
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}
	
//-------------------> updates the status and resolution of a given task and the designated employees <----------------------------------------------------------------------------------------	
	
	public boolean updateTaskStatusAndResolution(Integer fromEmployeeId, Integer toEmployeeId){
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement updateTaskStatusResolution = (PreparedStatement) con.prepareStatement("insert into statusResolutions(statusId, resolutionId, fromEmployeeId, toEmployeeId, taskId) values(?, ?, ?, ?, ?)");
			updateTaskStatusResolution.setInt(1, statusId);
			updateTaskStatusResolution.setInt(2, resolutionId);
			updateTaskStatusResolution.setInt(3, fromEmployeeId);
			updateTaskStatusResolution.setInt(4, toEmployeeId);
			updateTaskStatusResolution.setInt(5, this.getTaskId());
			Integer result = updateTaskStatusResolution.executeUpdate();
			
			if (result == 1){
				isExecuted = true;
				return isExecuted;
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}
	
//-------------------> assigns an employee to a specific task(the employee must be part of the team) <-----------------------------------------------------------------------------------------	
	
	public boolean assignEmployeeToTask(Integer employeeId){
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement assignEmployeeToTask = (PreparedStatement) con.prepareStatement("update teamTasks set employeeId = ? where taskId = ?");
			assignEmployeeToTask.setInt(1, employeeId);
			assignEmployeeToTask.setInt(2, this.getTaskId());
			Integer result = assignEmployeeToTask.executeUpdate();
			
			if (result == 1){
				isExecuted = true;
				return isExecuted;
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}
	
//-------------------------------> Before assigning the task to a different employee the curent employee sets the effort spent on this task <---------------------------------------------------	
	
	public boolean assignTaskEffortToTask(Integer employeeId, Double employeeTaskEffort){
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement assignEmployeeToTask = (PreparedStatement) con.prepareStatement("insert into teamTasks(employeeTaskEffort) values (?) where employeeId = ? and taskId = ?");
			assignEmployeeToTask.setDouble(1, employeeTaskEffort);
			assignEmployeeToTask.setInt(2, employeeId);
			assignEmployeeToTask.setInt(3, this.getTaskId());
			Integer result = assignEmployeeToTask.executeUpdate();
			
			if (result == 1){
				isExecuted = true;
				return isExecuted;
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}
//-------------------> add's a dependency between 2 tasks <------------------------------------------------------------------------------------------------------------------------------------	
	
	public boolean addTaskDependency(Integer sourceTaskId, Integer destinationTaskId, Integer dependencyId){
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement createTaskDependency = (PreparedStatement) con.prepareStatement("insert into taskDependencies (sourceTaskId, destinationTaskId, dependencyId) values (?, ?, ?)");
			createTaskDependency.setInt(1, sourceTaskId);
			createTaskDependency.setInt(2, destinationTaskId);
			createTaskDependency.setDouble(3, dependencyId);
			Integer result = createTaskDependency.executeUpdate();
			
			if (result == 1){
				isExecuted = true;
				return isExecuted;
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}
	
//-------------------> assign a task to a different milestone <--------------------------------------------------------------------------------------------------------------------------------	
	
	public boolean assignTaskToDifferentMilestone(Integer milestoneId){
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement changeMilestone = (PreparedStatement) con.prepareStatement("update tasks set milestoneId = ? where taskId = ?");
			changeMilestone.setInt(1, milestoneId);
			changeMilestone.setInt(2, this.getTaskId());
			Integer result = changeMilestone.executeUpdate();
			
			if (result == 1){
				isExecuted = true;
				return isExecuted;
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}

//---------------------------> Searches for all the tasks that an employee has or will have(tasks that don't have the status closed) <-----------------------------------------

	@SuppressWarnings("rawtypes")
	public Hashtable queryFutureTasks(Integer employeeId){
		Connection con = null;
		Hashtable<Integer, ArrayList<String>> info = new Hashtable<Integer, ArrayList<String>>();  							//CNPI - code-name-ptojectId
		
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement query = (PreparedStatement) con.prepareStatement("select t.taskId, t.taskName, p.code, datediff(t.startDate, sysdate()) from teamTasks tt, tasks t, milestones m, projects p, statusResolutions sr where tt.taskId = t.taskId and t.milestoneId = m.milestoneId and m.projectId = p.projectId and sr.taskId = t.taskId and sr.statusId not in (4) and tt.employeeId = ? order by t.startDate asc LIMIT 5");
			query.setInt(1, employeeId);
			ResultSet rs = query.executeQuery();
			
			while(rs.next()){
				ArrayList<String> projectInfo = new ArrayList<String>();
				projectInfo.add(rs.getString(2));                         // 1. am adaugat in array list numele taskului
				projectInfo.add(rs.getString(3));						  // 2. am adaugat in array list codul proiectului	
				projectInfo.add(rs.getString(4));						  // 3. am adaugat in array list numarul de zile ramase / overdue
				info.put(rs.getInt(1), projectInfo);                      // 3. am adaugat in hashmap pe 1 pozitie taskId'ul si pe a 2'a ArrayList'ul 
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return info;
	}
	
	@SuppressWarnings("rawtypes")
	public Hashtable queryTasksByMilestoneId(){
		Connection con = null;
		Hashtable<Integer, ArrayList<String>> info = new Hashtable<Integer, ArrayList<String>>();
		
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement query = (PreparedStatement) con.prepareStatement("select distinct(ta.taskId),ta.taskName, sr.creationDate, datediff(ta.startDate, sysdate()), sr.statusId from tasks ta, statusResolutions sr where milestoneId = ? and ta.taskId = sr.taskId order by sr.taskId, sr.creationDate desc");
			query.setInt(1, this.getMilestoneId());
			ResultSet rs = query.executeQuery();
			Integer taskId = 0; 
			while (rs.next()){
				if (taskId == 0){
					taskId = rs.getInt(1);
					ArrayList<String> taskInfo = new ArrayList<String>();
					taskInfo.add(rs.getString(2));                         // 1. am adaugat in array list numele taskului
					taskInfo.add(rs.getString(3));						   // 2. am adaugat in array list data crearii taskului	
					taskInfo.add(rs.getString(4));						   // 3. am adaugat in array list numarul de zile ramase / overdue
					taskInfo.add(rs.getString(5));						   // 4. am adaugat in array list statusId-ul taskului
					info.put(rs.getInt(1), taskInfo);                      // 3. am adaugat in hashmap pe 1 pozitie taskId'ul si pe a 2'a ArrayList'ul 
				}
				else if (taskId == rs.getInt(1) ) {
					taskId = rs.getInt(1);
				}
				else{
					taskId = rs.getInt(1);
					ArrayList<String> taskInfo = new ArrayList<String>();
					taskInfo.add(rs.getString(2));                         // 1. am adaugat in array list numele taskului
					taskInfo.add(rs.getString(3));						   // 2. am adaugat in array list data crearii taskului	
					taskInfo.add(rs.getString(4));						   // 3. am adaugat in array list numarul de zile ramase / overdue
					taskInfo.add(rs.getString(5));						   // 4. am adaugat in array list statusId-ul taskului
					info.put(rs.getInt(1), taskInfo);                      // 3. am adaugat in hashmap pe 1 pozitie taskId'ul si pe a 2'a ArrayList'ul 
				}
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return info;
	}
	
	@SuppressWarnings("rawtypes")
	public ArrayList queryTaskInfo(){
		Connection con = null;
		ArrayList<String> info = new ArrayList<String>();  							//CNPI - code-name-ptojectId
		
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement query = (PreparedStatement) con.prepareStatement("select t.taskId, t.startDate, t.endDate, t.taskName, t.taskDescription, tp.taskPriorityName, tat.taskTypeName, t.taskEffort, s.statusName, r.resolutionName, concat(c.firstName,' ',c.lastName)from tasks t, taskPriorities tp, taskTypes tat, statusResolutions sr, statuses s, resolutions r, teamTasks tet, licenseDb.Customer c where t.taskPriorityId = tp.taskPriorityId and t.taskTypeId = tat.taskTypeId and t.taskId = tet.taskId and tet.employeeId = c.customerId and t.taskId = sr.taskId and sr.statusId = s.statusId and sr.resolutionId = r.resolutionId and t.taskId = ? order by sr.creationDate desc LIMIT 1");
			query.setInt(1, this.getTaskId());
			ResultSet rs = query.executeQuery();
			
			while(rs.next()){
				info.add(rs.getString(1));						  //    am adaugat in pe 1 pozitie taskId'ul
				info.add(rs.getString(2));                        // 1. am adaugat in array list startDate
				info.add(rs.getString(3));						  // 2. am adaugat in array list endDate	
				info.add(rs.getString(4));						  // 3. am adaugat in array list task Name
				info.add(rs.getString(5));						  // 3. am adaugat in array list task Description
				info.add(rs.getString(6));						  // 3. am adaugat in array list task Priority name
				info.add(rs.getString(7));						  // 3. am adaugat in array list task type name
				info.add(rs.getString(8));						  // 3. am adaugat in array list task effort
				info.add(rs.getString(9));						  // 3. am adaugat in array list task status
				info.add(rs.getString(10));						  // 3. am adaugat in array list task resolution 
				info.add(rs.getString(11));
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return info;
	}
	
	public Hashtable<Integer, String> queryEmployees(String fullName){
		Connection con = null;
		Hashtable<Integer, String> matches = new Hashtable<Integer, String>();
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement queryEmployee = (PreparedStatement) con.prepareStatement("select distinct (c.customerId), concat(c.firstName,' ',c.lastName) from licenseDb.Customer c, projectsDb.team t, projectsDb.milestones m, tasks ta where t.employeeId = c.customerId and ta.milestoneId = m.milestoneId and m.projectId = t.projectId and ta.taskId = ? and (lower(c.firstName) like ? or lower(c.lastName) like ?)");
			queryEmployee.setInt(1, this.getTaskId());
			queryEmployee.setString(2, "%" + fullName + "%");
			queryEmployee.setString(3, "%" + fullName + "%");
			ResultSet rs = queryEmployee.executeQuery();
			
			while (rs.next()){
				 matches.put(rs.getInt(1), rs.getString(2));
			}
			return matches;
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return matches;
	}
	
	public Integer queryMilestoneId(){
		Connection con = null;
		Integer milestoneId = 0;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement queryEmployee = (PreparedStatement) con.prepareStatement("select milestoneId from tasks where taskId = ?");
			queryEmployee.setInt(1, this.getTaskId());
			ResultSet rs = queryEmployee.executeQuery();
			
			while (rs.next()){
				milestoneId = rs.getInt(1);
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return milestoneId;
	}
	
	public Integer queryProjectId(){
		Connection con = null;
		Integer projectId = 0;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement queryEmployee = (PreparedStatement) con.prepareStatement("select m.projectId from milestones m, tasks t where m.milestoneId = t.milestoneId and t.taskId = ?");
			queryEmployee.setInt(1, this.getTaskId());
			ResultSet rs = queryEmployee.executeQuery();
			
			while (rs.next()){
				projectId = rs.getInt(1);
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return projectId;
	}
	
	public static void main(String args[]) throws ParseException{
		task x = new task();
//		x.setTaskId(9);
//		System.out.println(x.queryEmployees("uuu"));
		x.setStatusId(1);
		x.setResolutionId(1);
		x.setMilestoneId(5);
		x.setStartDate("2011-10-12");
		x.setEndDate("2011-10-22");
		x.setTaskName("taskName 20 test");
		x.setTaskDescription("Description20 test");
		x.setTaskPriorityId(2);
		x.setTaskTypeId(2);
		x.setTaskEffort(10.2);
//		x.setTaskId(3);
		System.out.println(x.createTask(3));
//		x.addTaskDependency(3, 3, 4);
//		System.out.println(x.updateTask());
//		x.assignEmployeeToTask(1, 50.0);
//		x.asignTaskToDifferentMilestone(4);
//		x.setTaskId(9);
//		x.assignEmployeeToTask(3, 10.2);
//		System.out.println(x.queryFutureTasks(2));
//		System.out.println(x.queryProjectId());
		
	}
}
